﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Xml;
using System.Windows.Forms;


namespace DuAnNH
{
    class Coso
    {
        public static SqlConnection con;
        public static bool MoKetNoi(string sv, string csdl, string un, string pwd)
        {
            try
            {
                string str = string.Format(@"Data Source={0};Initial Catalog={1};User ID={2};Password={3}", sv, csdl, un, pwd);
                con = new SqlConnection(str);
                con.Open();
                return true;
                //Integrated Security=True;AttachDBFilename=|DataDirectory|NhaHang.mdf;
            }
            catch
            {
                return false;
            }
        }
        
        public static bool KetNoiSQL()
        {
            try
            {
                string str = string.Format(@"Data Source=.\SQL2005;Integrated Security=true; AttachDbFilename=|DataDirectory|\laptopdtdd.mdf;User Instance=true;");
                //string str = string.Format(@"Server=.\SQLExpress;AttachDbFilename=|DataDirectory|laptopdtdd.mdf; Database=laptopdtdd;Trusted_Connection=Yes;");
                con = new SqlConnection(str);
                con.Open();
                return true;
            }
            catch
            {
                return false;
            }
        }

        public static DataTable LayBang(string tb)
        {
            string str = "Select * From " + tb;
            SqlDataAdapter sdap = new SqlDataAdapter(str,Coso.con);
            DataTable DT = new DataTable();
            sdap.Fill(DT);
            return DT;
        }

        public static DataTable LoadBanGoi()
        {
            string str = "Select * From Ban WHERE TinhTrang=0";
            SqlDataAdapter sdap = new SqlDataAdapter(str, Coso.con);
            DataTable DT = new DataTable();
            sdap.Fill(DT);
            return DT;
        }
        public static DataTable LoadBanCapNhat()
        {
            string str = "Select * From Ban WHERE TinhTrang=1";
            SqlDataAdapter sdap = new SqlDataAdapter(str, Coso.con);
            DataTable DT = new DataTable();
            sdap.Fill(DT);
            return DT;
        }

        public static DataTable LoadUser(string un, string pwd)
        {
            try
            {
                string str = string.Format("Select * From NhanVien Where TenDN='{0}' And MatKhau='{1}'", un, pwd);
                SqlDataAdapter sdap = new SqlDataAdapter(str, Coso.con);
                DataTable DT = new DataTable();
                sdap.Fill(DT);
                return DT;
            }
            catch
            {
                return null;
            }
        }

        public static DataTable LoadRole(string un, string pwd)
        {
            try
            {
                string str = string.Format("Select HoTen From NhanVien Where TenDN='{0}' And MatKhau='{1}'", un, pwd);
                SqlDataAdapter sdap = new SqlDataAdapter(str, Coso.con);
                DataTable DT = new DataTable();
                sdap.Fill(DT);
                return DT;
            }
            catch
            {
                return null;
            }
        }
        public static DataTable EditBan(string un, string pwd)
        {
            try
            {
                string str = string.Format("Update Ban Set GhepBan='{0}' Where MaBan='{1}'", un, pwd);
                SqlDataAdapter sdap = new SqlDataAdapter(str, Coso.con);
                DataTable DT = new DataTable();
                sdap.Fill(DT);
                return DT;
            }
            catch
            {
                return null;
            }
        }

        public static DataTable LayBangID(string tb,string id)
        {
            string str = "Select * From " + tb + " where MaLoai=" + id;
            SqlDataAdapter sdap = new SqlDataAdapter(str, Coso.con);
            DataTable DT = new DataTable();
            sdap.Fill(DT);
            return DT;
        }
        public static void them_mhd(string maTD, string tenTD, string sl,int gia)
        {
            string str = "insert_mhd " + maTD + ",N'" + tenTD + "','" + sl + ",'" + gia + "'";
            SqlCommand com = new SqlCommand(str, Coso.con);
            com.ExecuteNonQuery();
        }

        //public static bool kt;

        

        //public static DataTable Update_Ban(string MaBan)
        //{
        //    //ClsKetNoiCSDL kn=new ClsKetNoiCSDL();
        //    string str = "Update_TTBan'" + MaBan + "'";
        //    SqlCommand com = new SqlCommand(str, Coso.con);
        //    com.ExecuteNonQuery();
        //}
    }
}
